# -*- coding: utf-8 -*-
"""
商品数据访问层 - 修复版

负责商品相关的数据库操作
"""
from typing import List, Dict, Any, Optional, Tuple
from app.repositories.base_repository import BaseRepository
import re
import pymysql
import logging
from app.sql.database_config import execute_query, get_db_connection, DatabaseConfig

logger = logging.getLogger(__name__)


class GoodsRepository(BaseRepository):
    def __init__(self):
        super().__init__()
        self.connection = None
    
    def get_connection(self):
        """获取数据库连接"""
        if not self.connection:
            self.connection = pymysql.connect(**DatabaseConfig.get_connection_params())
        return self.connection
    
    def get_goods_list(self, page=1, page_size=10, **filters):
        """获取商品列表 - 修复校对规则冲突"""
        try:
            # 基础查询 - 添加校对规则
            query = """
            SELECT g.*, b.brand_name, c.class_name as category_name
            FROM goods g 
            LEFT JOIN goods_brand b ON g.brand_id COLLATE utf8mb4_unicode_ci = b.brand_id COLLATE utf8mb4_unicode_ci
            LEFT JOIN goods_class c ON g.class_id COLLATE utf8mb4_unicode_ci = c.class_id COLLATE utf8mb4_unicode_ci
            WHERE 1=1
            """
            
            params = []
            
            # 添加过滤条件
            if filters.get('keyword'):
                query += " AND (g.goods_name LIKE %s OR g.short_name LIKE %s OR g.goods_no LIKE %s)"
                keyword = f"%{filters['keyword']}%"
                params.extend([keyword, keyword, keyword])
            
            if filters.get('category_id'):
                query += " AND g.class_id = %s"
                params.append(filters['category_id'])
            
            if filters.get('brand_id'):
                query += " AND g.brand_id = %s"
                params.append(filters['brand_id'])
            
            # 添加排序
            query += " ORDER BY g.modified DESC"
            
            # 添加分页
            offset = (page - 1) * page_size
            query += " LIMIT %s OFFSET %s"
            params.extend([page_size, offset])
            
            logger.info(f"执行商品查询: {query}")
            logger.info(f"参数: {params}")
            
            results = self.execute_query(query, params)
            logger.info(f"查询结果数量: {len(results)}")
            
            # 如果需要包含规格信息
            if filters.get('include_specs'):
                for item in results:
                    specs = self.get_goods_specs(item['goods_id'])
                    item['specs'] = specs
                    logger.info(f"商品 {item['goods_id']} 规格数量: {len(specs)}")
            
            # 获取总数 - 添加校对规则
            count_query = """
            SELECT COUNT(*) as total 
            FROM goods g 
            LEFT JOIN goods_brand b ON g.brand_id COLLATE utf8mb4_unicode_ci = b.brand_id COLLATE utf8mb4_unicode_ci
            LEFT JOIN goods_class c ON g.class_id COLLATE utf8mb4_unicode_ci = c.class_id COLLATE utf8mb4_unicode_ci
            WHERE 1=1
            """
            
            # 重新构建WHERE条件（不包含分页）
            count_params = []
            if filters.get('keyword'):
                count_query += " AND (g.goods_name LIKE %s OR g.short_name LIKE %s OR g.goods_no LIKE %s)"
                keyword = f"%{filters['keyword']}%"
                count_params.extend([keyword, keyword, keyword])
            
            if filters.get('category_id'):
                count_query += " AND g.class_id = %s"
                count_params.append(filters['category_id'])
            
            if filters.get('brand_id'):
                count_query += " AND g.brand_id = %s"
                count_params.append(filters['brand_id'])
            
            total_result = self.execute_single_query(count_query, count_params)
            total = total_result['total'] if total_result else 0
            
            logger.info(f"总数: {total}")
            
            return {
                'items': results,
                'total': total,
                'page': page,
                'page_size': page_size,
                'total_pages': (total + page_size - 1) // page_size,
                'include_specs': filters.get('include_specs', False),
                'include_sales': filters.get('include_sales', False),
                'current_page': page,
                'last_page': (total + page_size - 1) // page_size
            }
            
        except Exception as e:
            logger.error(f"获取商品列表失败: {str(e)}")
            import traceback
            logger.error(f"错误详情: {traceback.format_exc()}")
            return {
                'items': [],
                'total': 0,
                'page': page,
                'page_size': page_size,
                'total_pages': 0,
                'include_specs': filters.get('include_specs', False),
                'include_sales': filters.get('include_sales', False),
                'current_page': page,
                'last_page': 0
            }
    
    def find_goods_list_with_search(self, page=1, page_size=10, **filters):
        """兼容旧方法名"""
        return self.get_goods_list(page, page_size, **filters)
    
    def get_goods_by_id(self, goods_id: str):
        """根据ID获取商品详情"""
        try:
            query = """
            SELECT g.*
            FROM goods g 
            WHERE g.goods_id = %s AND g.deleted != '1'
            """
            result = self.execute_query(query, (goods_id,))
            
            # 如果需要关联信息，单独查询
            if result and len(result) > 0:
                item = result[0]
                if isinstance(item, dict) and item.get('brand_id'):
                    brand_info = self._get_brand_info(item['brand_id'])
                    if brand_info:
                        item['brand_name'] = brand_info.get('brand_name', '')
            
            return result
            
        except Exception as e:
            logger.error(f"获取商品详情失败: {e}")
            return []
    
    def get_goods_detail(self, goods_id: str, include_specs: bool = True) -> Optional[Dict]:
        """获取商品详情 - 修复校对规则冲突"""
        try:
            query = """
            SELECT g.*, b.brand_name, c.class_name as category_name
            FROM goods g 
            LEFT JOIN goods_brand b ON g.brand_id COLLATE utf8mb4_unicode_ci = b.brand_id COLLATE utf8mb4_unicode_ci
            LEFT JOIN goods_class c ON g.class_id COLLATE utf8mb4_unicode_ci = c.class_id COLLATE utf8mb4_unicode_ci
            WHERE g.goods_id = %s
            LIMIT 1
            """
            
            result = self.execute_single_query(query, [goods_id])
            
            if result and include_specs:
                specs = self.get_goods_specs(goods_id)
                result['specs'] = specs
            
            return result
            
        except Exception as e:
            logger.error(f"获取商品详情失败: {str(e)}")
            return None
    
    def get_goods_specs(self, goods_id: str) -> List[Dict]:
        """获取商品规格信息 - 使用正确的价格字段"""
        try:
            # 先检查goods_spec表是否存在
            check_query = "SHOW TABLES LIKE 'goods_spec'"
            table_exists = self.execute_single_query(check_query)
            
            if not table_exists:
                logger.info("goods_spec表不存在，返回空列表")
                return []
            
            # 获取表结构
            describe_query = "DESCRIBE goods_spec"
            columns_result = self.execute_query(describe_query)
            column_names = [col['Field'] for col in columns_result]
            
            logger.info(f"goods_spec表字段: {column_names}")
            
            # 构建查询字段，优先使用正确的价格字段
            select_fields = []
            
            # 基础字段
            base_fields = ['spec_id', 'goods_id', 'spec_name', 'spec_code']
            for field in base_fields:
                if field in column_names:
                    select_fields.append(field)
            
            # 价格字段 - 优先使用retail_price和mustor_price1
            if 'retail_price' in column_names:
                select_fields.append('retail_price')
            if 'custorm_price1' in column_names:
                select_fields.append('custorm_price1')
            
            # 如果上面两个价格字段都不存在，尝试其他价格字段
            if 'retail_price' not in column_names and 'custorm_price1' not in column_names:
                price_fields = ['spec_price', 'price', 'cost_price', 'sale_price']
                for field in price_fields:
                    if field in column_names:
                        select_fields.append(field)
                        break
            
            # 其他字段
            other_fields = ['spec_stock', 'spec_status', 'spec_sort', 'spec_image', 'spec_weight', 'spec_volume']
            for field in other_fields:
                if field in column_names:
                    select_fields.append(field)
            
            if not select_fields:
                logger.warning("goods_spec表没有可用字段")
                return []
            
            query = f"""
            SELECT {', '.join(select_fields)}
            FROM goods_spec
            WHERE goods_id = %s
            """
            
            # 如果有spec_status字段，添加状态过滤
            if 'spec_status' in column_names:
                query += " AND spec_status = 1"
            
            # 如果有spec_sort字段，添加排序
            if 'spec_sort' in column_names:
                query += " ORDER BY spec_sort ASC, spec_id ASC"
            else:
                query += " ORDER BY spec_id ASC"
            
            results = self.execute_query(query, [goods_id])
            logger.info(f"获取商品规格: goods_id={goods_id}, count={len(results)}")
            
            # 为每个规格添加默认值和价格映射
            for spec in results:
                # 设置价格字段
                if 'retail_price' in spec:
                    spec['actual_price'] = spec['retail_price']  # 实际价格
                elif 'mustor_price1' in spec:
                    spec['actual_price'] = spec['custorm_price1']  # 自定义价格
                elif 'spec_price' in spec:
                    spec['actual_price'] = spec['spec_price']
                else:
                    spec['actual_price'] = 10.00  # 默认价格
                
                # 设置自定义价格
                if 'mustor_price1' in spec:
                    spec['custom_price'] = spec['mustor_price1']
                else:
                    spec['custom_price'] = spec.get('actual_price', 10.00)
                
                # 其他默认值
                if 'plat_spec_count' not in spec or not spec.get('plat_spec_count'):
                    spec['plat_spec_count'] = 100
                if 'img_url' not in spec or not spec.get('img_url'):
                    spec['spec_image'] = '/static/images/default_spec.jpg'
                if 'spec_status' not in spec:
                    spec['spec_status'] = 1
                if 'spec_sort' not in spec:
                    spec['spec_sort'] = 0
            
            return results
            
        except Exception as e:
            logger.error(f"获取商品规格失败: {str(e)}")
            return []
    
    def get_goods_brands(self, page=1, page_size=10, **filters):
        """获取商品品牌列表"""
        query = """
        SELECT brand_id, brand_no, brand_name, remark, created_at
        FROM goods_brand
        WHERE 1=1
        """
        
        params = []
        
        if filters.get('keyword'):
            query += " AND (brand_name LIKE %s OR brand_no LIKE %s)"
            keyword = f"%{filters['keyword']}%"
            params.extend([keyword, keyword])
        
        query += " ORDER BY created_at DESC LIMIT %s OFFSET %s"
        params.extend([page_size, (page - 1) * page_size])
        
        return execute_query(query, tuple(params) if params else None)
    
    def get_goods_classes(self, page=1, page_size=10, **filters):
        """获取商品分类列表"""
        query = """
        SELECT class_id, pareant_id, is_leaf, class_name, path, icon, gradient, created, modified
        FROM goods_class
        WHERE 1=1
        """
        
        params = []
        
        if filters.get('parent_id'):
            query += " AND pareant_id = %s"
            params.append(filters['parent_id'])
        
        if filters.get('is_leaf'):
            query += " AND is_leaf = %s"
            params.append(filters['is_leaf'])
        
        query += " ORDER BY created DESC LIMIT %s OFFSET %s"
        params.extend([page_size, (page - 1) * page_size])
        
        return execute_query(query, tuple(params) if params else None)
    
    def _get_brand_info(self, brand_id):
        """单独获取品牌信息"""
        try:
            query = "SELECT brand_name FROM goods_brand WHERE brand_id = %s"
            result = self.execute_query(query, (brand_id,))
            
            if result and len(result) > 0:
                return result[0]
            return None
            
        except Exception as e:
            logger.error(f"获取品牌信息失败: {e}")
            return None
    
    def close_connection(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
    
    


class GoodsCommentsRepository(BaseRepository):
    """商品评论数据访问类"""
    
    def __init__(self):
        super().__init__("goods_comments")
    
    def find_comments_by_goods_id(self, goods_id: str, page: int = 1, 
                                 page_size: int = 10) -> Dict[str, Any]:
        """根据商品ID查询评论列表"""
        # 计算总数
        count_sql = """
            SELECT COUNT(*) as total
            FROM goods_comments gc
            WHERE gc.goods_id = %s 
        """
        
        total_result = self.execute_custom_query(count_sql, [goods_id], True)
        total = total_result[0]['total'] if total_result else 0
        
        # 获取分页数据
        offset = (page - 1) * page_size
        
        comments_sql = """
            SELECT 
                gc.comment_id,
                gc.goods_id,
                gc.customer_name,
                gc.customer_avatar,
                gc.rating,
                gc.comment,
                gc.images,
                gc.created_time,
                gc.reply_content,
                gc.reply_time,
                g.goods_name,
                g.goods_no
            FROM goods_comments gc
            LEFT JOIN goods g ON gc.goods_id = g.goods_id
            WHERE gc.goods_id = %s 
            ORDER BY gc.created DESC
            LIMIT %s OFFSET %s
        """
        
        comments = self.execute_custom_query(comments_sql, [goods_id, page_size, offset], True) or []
        
        return {
            "total": total,
            "items": comments
        }
    
    def get_comments_statistics(self, goods_id: str) -> Dict[str, Any]:
        """获取商品评论统计信息"""
        stats_sql = """
            SELECT 
                COUNT(*) as total_comments,
                COUNT(CASE WHEN rating >= 5 THEN 1 END) as high_rating_comments,
                AVG(CAST(rating AS DECIMAL(3,2))) as average_rating,
                COUNT(CASE WHEN rating = 5 THEN 1 END) as five_star_comments,
                COUNT(CASE WHEN rating = 4 THEN 1 END) as four_star_comments,
                COUNT(CASE WHEN rating = 3 THEN 1 END) as three_star_comments,
                COUNT(CASE WHEN rating = 2 THEN 1 END) as two_star_comments,
                COUNT(CASE WHEN rating = 1 THEN 1 END) as one_star_comments
            FROM goods_comments 
            WHERE goods_id = %s
        """
        
        result = self.execute_custom_query(stats_sql, [goods_id], True)
        if not result:
            return {
                "total_comments": 0,
                "high_rating_comments": 0,
                "average_rating": 0.0,
                "five_star_comments": 0,
                "four_star_comments": 0,
                "three_star_comments": 0,
                "two_star_comments": 0,
                "one_star_comments": 0
            }
        
        stats = result[0]
        # 处理平均评分，如果为None则设为0
        if stats['average_rating'] is not None:
            stats['average_rating'] = float(stats['average_rating'])
        else:
            stats['average_rating'] = 0.0
            
        return stats


class GoodsBrandRepository(BaseRepository):
    """商品品牌数据访问类"""
    
    def __init__(self):
        super().__init__("goods_brand")
    
    def find_brands_with_search(self, brand_name: Optional[str] = None,
                               page: int = 1, page_size: int = 10) -> Dict[str, Any]:
        """查询品牌列表（支持搜索和分页）"""
        conditions = {"is_disabled": "0"}  # 只查询未禁用的品牌
        
        if brand_name:
            # 这里需要特殊处理LIKE查询，暂时用基础条件
            pass
        
        # 使用自定义查询来支持LIKE搜索
        where_conditions = ["is_disabled != '1'"]
        params = []
        
        if brand_name:
            where_conditions.append("brand_name LIKE %s")
            params.append(f"%{brand_name}%")
        
        where_clause = " AND ".join(where_conditions)
        
        # 计算总数
        count_sql = f"""
            SELECT COUNT(*) as total
            FROM goods_brand
            WHERE {where_clause}
        """
        
        total_result = self.execute_custom_query(count_sql, params, True)
        total = total_result[0]['total'] if total_result else 0
        
        # 获取分页数据
        offset = (page - 1) * page_size
        brand_sql = f"""
            SELECT 
                brand_id,
                brand_no,
                brand_name,
                remark,
                created_at
            FROM goods_brand
            WHERE {where_clause}
            ORDER BY created_at DESC
            LIMIT %s OFFSET %s
        """
        
        params.extend([page_size, offset])
        brands = self.execute_custom_query(brand_sql, params, True) or []
        
        return {
            "total": total,
            "items": brands
        }


class GoodsClassRepository(BaseRepository):
    """商品分类数据访问类"""
    
    def __init__(self):
        super().__init__("goods_class")
    
    def find_categories_with_search(self, class_name: Optional[str] = None,
                                   page: int = 1, page_size: int = 10) -> Dict[str, Any]:
        """查询分类列表（支持搜索和分页）"""
        where_conditions = []
        params = []
        
        if class_name:
            where_conditions.append("class_name LIKE %s")
            params.append(f"%{class_name}%")
        
        where_clause = ""
        if where_conditions:
            where_clause = "WHERE " + " AND ".join(where_conditions)
        
        # 计算总数
        count_sql = f"""
            SELECT COUNT(*) as total
            FROM goods_class
            {where_clause}
        """
        
        total_result = self.execute_custom_query(count_sql, params, True)
        total = total_result[0]['total'] if total_result else 0
        
        # 获取分页数据
        offset = (page - 1) * page_size
        categories_sql = f"""
            SELECT 
                class_id,
                pareant_id,
                is_leaf,
                class_name,
                path,
                icon,
                gradient,
                created,
                modified
            FROM goods_class
            {where_clause}
            ORDER BY created DESC
            LIMIT %s OFFSET %s
        """
        
        params.extend([page_size, offset])
        categories = self.execute_custom_query(categories_sql, params, True) or []
        
        return {
            "total": total,
            "items": categories
        }
    
    def find_all_categories(self) -> List[Dict[str, Any]]:
        """获取所有分类（用于构建树形结构）"""
        sql = """
            SELECT 
                class_id,
                pareant_id,
                is_leaf,
                class_name,
                path,
                icon,
                gradient,
                created,
                modified
            FROM goods_class
            ORDER BY class_id ASC
        """
        
        categories = self.execute_custom_query(sql, [], True) or []
        return categories
    
    def find_goods_count_by_category(self, class_id: str) -> int:
        """根据分类ID查询商品数量"""
        sql = """
            SELECT COUNT(*) as count
            FROM goods
            WHERE class_id = %s AND deleted != '1'
        """
        
        result = self.execute_custom_query(sql, [class_id], True)
        return result[0]['count'] if result else 0

    def get_goods_categories(self, parent_id: str = "0", page: int = 1, page_size: int = 20) -> Dict[str, Any]:
        """获取商品分类"""
        try:
            # 先检查goods_class表是否存在
            check_query = "SHOW TABLES LIKE 'goods_class'"
            table_exists = self.execute_single_query(check_query)
            
            if not table_exists:
                logger.info("goods_class表不存在，返回空列表")
                return {
                    'items': [],
                    'total': 0,
                    'page': page,
                    'page_size': page_size,
                    'total_pages': 0
                }
            
            query = """
            SELECT * FROM goods_class
            WHERE parent_id = %s
            ORDER BY class_sort ASC, class_id ASC
            """
            
            params = [parent_id]
            
            # 添加分页
            offset = (page - 1) * page_size
            query += " LIMIT %s OFFSET %s"
            params.extend([page_size, offset])
            
            results = self.execute_query(query, params)
            
            # 获取总数
            count_query = """
            SELECT COUNT(*) as total 
            FROM goods_class
            WHERE parent_id = %s
            """
            
            total_result = self.execute_single_query(count_query, [parent_id])
            total = total_result['total'] if total_result else 0
            
            return {
                'items': results,
                'total': total,
                'page': page,
                'page_size': page_size,
                'total_pages': (total + page_size - 1) // page_size
            }
            
        except Exception as e:
            logger.error(f"获取商品分类失败: {str(e)}")
            return {
                'items': [],
                'total': 0,
                'page': page,
                'page_size': page_size,
                'total_pages': 0
            } 
